package com.gthncz;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.gthncz.beans.Student;
import com.gthncz.beans.StudentDAO;

public class StudentDAOImpl implements StudentDAO {
	
	private DataSource dataSource; // 写在这里，为了 依赖注入使用
	private JdbcTemplate jdbcTemplate;
	
	protected class StudentMapper implements RowMapper<Student> {

		@Override
		public Student mapRow(ResultSet arg0, int rowNum) throws SQLException {
			Student student = new Student();
			student.setName(arg0.getString("name")); // getNString(); 
			student.setAge(arg0.getInt("age"));
			return student;
		}
		
	}

	@Override
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(dataSource); // 不需要关闭 ???
	}

	@Override
	public void insert(String name, Integer age) {
		String sql = "INSERT INTO `tb_student`(`name`, `age`) VALUES(?, ?) ";
		int res = jdbcTemplate.update(sql, name, age);
		System.out.printf(" Insert a new record (%s, %d) , result %d \n",  name, age, res);
	}

	@Override
	public Student getStudent(Integer id) {
		Student student = null;
		try {
			String sql = "SELECT * FROM `tb_student` WHERE `id`=? ";
			student = jdbcTemplate.queryForObject(sql, new Object[] {id}, new StudentMapper()); // org.springframework.dao.EmptyResultDataAccessException
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			e.printStackTrace();
		}
		return student;
	}

	@Override
	public List<Student> listStudents() {
		List<Student> students = null;
		try {
			String sql = "SELECT * FROM `tb_student` ";
			 students = jdbcTemplate.query(sql, new StudentMapper());
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			e.printStackTrace();
		}
		return students;
	}

	@Override
	public void delete(Integer id) {
		String  sql = "DELETE FROM `tb_student` WHERE `id`=? ";
		int res = jdbcTemplate.update(sql, id);
		System.out.printf(" Delete a record id=%d , result %d \n",  id, res);
	}
	
	
	@Override
	public void deleteAll() {
		String sql = "DELETE FROM `tb_student`";
		int res = jdbcTemplate.update(sql);
		System.out.printf(" Delete all records , result %d \n", res);
	}

	@Override
	public void update(Integer id, Integer age) {
		String sql = "UPDATE `tb_student` SET `age`=? WHERE `id`=? ";
		int res = jdbcTemplate.update(sql, age, id);
		System.out.printf(" Update a record id=%d , result %d \n",  id, res);
	}

}
